{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "from sklearn import preprocessing   #导出数据预处理包\n",
    "import numpy as np    #导入np数据\n",
    "import joblib\n",
    "import pymysql\n",
    "import json\n",
    "#导入模型\n",
    "km=joblib.load(filename='km.model')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### tip:单个dataframe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "data={'Chinese':[25],'Math':[100]}\n",
    "df=pd.DataFrame(data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[2]\n"
     ]
    }
   ],
   "source": [
    "labels3 = km.predict(df)\n",
    "print(labels3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 数据库"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "def sop():#启动连接\n",
    "    return pymysql.connect(host=\"localhost\",user=\"root\",passwd=\"nzzfl2\",db=\"python\",port=3306)#数据库连接"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "code_folding": []
   },
   "outputs": [],
   "source": [
    "def ins(sjk,sql):#插入sql\n",
    "    c=sjk.cursor()\n",
    "    try:\n",
    "        c.execute(sql)\n",
    "        sjk.commit()\n",
    "    except:\n",
    "        sjk.rollback()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "def sel(sjk,sql):#查询sql\n",
    "    c=sjk.cursor()\n",
    "    try:\n",
    "        c.execute(sql)\n",
    "        return c.fetchall()\n",
    "    except:\n",
    "        sjk.rollback()\n",
    "        return None"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "def stop(sjk):#关闭数据库\n",
    "    sjk.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 学生成绩"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "code_folding": []
   },
   "outputs": [],
   "source": [
    "#聚类学生成绩并保存至数据库\n",
    "def pre(iris_data):\n",
    "    if 'sno' in iris_data.columns and 'Chinese' in iris_data.columns and 'Math' in iris_data.columns: #上传文件检验\n",
    "        iris_data.dropna(inplace = True)#去除有空值的数据\n",
    "        #去除异常数据\n",
    "        iris_data[iris_data<0]=0\n",
    "        #iris_data[iris_data>100]=0\n",
    "        iris_data_2=iris_data[['Chinese','Math']]\n",
    "        kmpre_data = km.predict(iris_data_2)\n",
    "        sjkobj=sop()\n",
    "        try:\n",
    "            length=len(kmpre_data)\n",
    "            for i in range(0,length):\n",
    "                iris=iris_data.iloc[i]\n",
    "                ins(sjkobj,\"INSERT INTO stu(sno,Chinese,Math,type) VALUES('{}','{}','{}','{}')\".format(iris[0],iris[1],iris[2],kmpre_data[i]))\n",
    "            stop(sjkobj)\n",
    "            return str(kmpre_data[0]) #强转成字符串 服务器返回值不能是int\n",
    "        except:\n",
    "            return 'fail'\n",
    "    else:\n",
    "        return None"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "#上传文件\n",
    "def kmpre(file):\n",
    "    return pre(pd.read_csv('upload/'+file,sep = ','))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "#预测单个学生\n",
    "def stupre(data):\n",
    "    if data.empty: #DataFrame判断是否为空\n",
    "        return 'fail'\n",
    "    else:\n",
    "        try:\n",
    "            data = data.astype(np.int64) #强制转换为int类型\n",
    "            return pre(data)\n",
    "        except:\n",
    "            return 'fail'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "code_folding": []
   },
   "outputs": [],
   "source": [
    "#查询\n",
    "def selectstu(token,inf):\n",
    "    sjkobj=sop()\n",
    "    data=None\n",
    "    if token == '1': #查询全体\n",
    "        data=sel(sjkobj,\"select * from stu\")\n",
    "    if token == '2': #查询单个\n",
    "        data=sel(sjkobj,\"select * from stu where sno=\"+inf)\n",
    "    if token == '3': #查询类别\n",
    "        data=sel(sjkobj,\"select * from stu where type=\"+inf)\n",
    "    if data != None:\n",
    "        stop(sjkobj)\n",
    "        return stujson(data)\n",
    "\n",
    "def stujson(data): #学生json\n",
    "    jsonData=[]\n",
    "    for row in data:\n",
    "        result={}\n",
    "        result['sno']=row[0]\n",
    "        result['Chinese']=row[1]\n",
    "        result['Math']=row[2]\n",
    "        result['type']=row[3]\n",
    "        jsonData.append(result)\n",
    "    data={}\n",
    "    data['type']='success'\n",
    "    data['data']=jsonData\n",
    "    return data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 服务器"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "code_folding": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * Serving Flask app \"__main__\" (lazy loading)\n",
      " * Environment: production\n",
      "   WARNING: This is a development server. Do not use it in a production deployment.\n",
      "   Use a production WSGI server instead.\n",
      " * Debug mode: off\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      " * Running on http://127.0.0.1:5000/ (Press CTRL+C to quit)\n",
      "127.0.0.1 - - [22/Jun/2021 10:00:36] \"\u001b[37mPOST /uploader HTTP/1.1\u001b[0m\" 200 -\n",
      "127.0.0.1 - - [22/Jun/2021 10:00:36] \"\u001b[33mGET /favicon.ico HTTP/1.1\u001b[0m\" 404 -\n"
     ]
    }
   ],
   "source": [
    "import os\n",
    "from flask import Flask, request, redirect, url_for,jsonify\n",
    "from werkzeug.utils import secure_filename\n",
    "#服务器配置\n",
    "app = Flask(__name__)\n",
    "UPLOAD_FOLDER = 'upload' #上传文件目录\n",
    "app.config['UPLOAD_FOLDER'] = UPLOAD_FOLDER\n",
    "app.config['JSON_AS_ASCII'] = False\n",
    "\n",
    "#单个学生\n",
    "@app.route('/stu',methods = ['POST', 'GET']) \n",
    "def stu():\n",
    "    if request.method == 'POST':\n",
    "         return  stupre(pd.DataFrame({'sno':[request.form['sno']],'Chinese':[request.form['Chinese']],'Math':[request.form['Math']]}))\n",
    "    else:\n",
    "         return '请使用post传递参数'\n",
    "        \n",
    "#上传文件      \n",
    "@app.route('/uploader',methods=['GET','POST'])\n",
    "def uploader():\n",
    "    if request.method == 'POST':\n",
    "        f = request.files['file']\n",
    "        f.save(os.path.join(app.config['UPLOAD_FOLDER'],secure_filename(f.filename)))\n",
    "        txt=kmpre(f.filename)\n",
    "        if  txt != 'fail':\n",
    "            if txt == None:\n",
    "                return '文件上传失败,请检查文件是否符合规范'\n",
    "            else: \n",
    "                return '文件上传成功'\n",
    "        else:\n",
    "            return '文件上传失败'\n",
    "    else:\n",
    "        return '请使用post传递参数'\n",
    "    \n",
    "#查询学生     \n",
    "@app.route('/selstu',methods=['GET','POST'])\n",
    "def selstu():\n",
    "    if request.method == 'POST':\n",
    "        return jsonify(selectstu(request.form['token'],request.form['information']))\n",
    "    else:\n",
    "        return selectstu(1,0)#默认返回全部\n",
    "    \n",
    "#服务器运行配置\n",
    "if __name__ == '__main__':\n",
    "    app.run(host='127.0.0.1',port='5000')"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.5"
  },
  "varInspector": {
   "cols": {
    "lenName": 16,
    "lenType": 16,
    "lenVar": 40
   },
   "kernels_config": {
    "python": {
     "delete_cmd_postfix": "",
     "delete_cmd_prefix": "del ",
     "library": "var_list.py",
     "varRefreshCmd": "print(var_dic_list())"
    },
    "r": {
     "delete_cmd_postfix": ") ",
     "delete_cmd_prefix": "rm(",
     "library": "var_list.r",
     "varRefreshCmd": "cat(var_dic_list()) "
    }
   },
   "types_to_exclude": [
    "module",
    "function",
    "builtin_function_or_method",
    "instance",
    "_Feature"
   ],
   "window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
